package Server.Database;

import Server.Project;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Paulius Dambrauskas
 */
public class ProjectRegister {

    private Database db;

    public ProjectRegister(Database db) {
        this.db = db;
    }

    public void addProject(String name, List<String> users) throws Exception {
        if (name.isEmpty() || projectExists(name)) {
            throw new IllegalArgumentException();
        } else {
            db.statement.executeUpdate("INSERT INTO projects (ProjectName)VALUES('" + name + "')");
            /*resultSet = statement.executeQuery("SELECT ProjectID FROM projects WHERE ProjectName='"+name+"' LIMIT 1");
             String project = "";
             while(resultSet.next()){
             project = resultSet.getString(1);
             }*/

            for (int i = 0; i < users.size(); i++) {
                //ŽIAURIAI optimaliai rišam pagal id, nors ir pavadinimai unikalūs... :D
                //statement.executeUpdate("INSERT INTO depends (User, Project)VALUES((SELECT User_ID FROM user WHERE Username='"+users.get(i)+"'), '"+project+"')");
                db.statement.executeUpdate("INSERT INTO depends (User, Project)VALUES('" + users.get(i) + "', '" + name + "')");
            }

        }
    }

    private boolean projectExists(String name) throws SQLException {
        db.resultSet = db.statement.executeQuery("SELECT ProjectName FROM projects WHERE ProjectName='" + name + "'"); //tarkim nebus vardas su " simboliu :D
        return db.resultSet.next();
    }

    public void deleteProject(String name) throws SQLException {
        db.statement.execute("DELETE FROM depends WHERE project='" + name + "'");
        db.statement.execute("DELETE FROM projects WHERE ProjectName='" + name + "' LIMIT 1");
    }

    public ArrayList<Project> getAllProjects() throws SQLException {

        ArrayList<Project> result = new ArrayList<Project>();

        db.resultSet = db.statement.executeQuery("SELECT * from projects");

        while (db.resultSet.next()) {
            result.add(new Project(db.resultSet.getInt(1), db.resultSet.getString(2)));
        }

        for (int j = 0; j < result.size(); j++) {

            /*resultSet = statement.executeQuery("SELECT A.Username FROM user AS A," +
             " depends AS B, projects AS C WHERE C.ProjectID = '"+result.get(j).getId()+"' AND " +
             "B.project = '"+result.get(j).getId()+"' AND B.user = A.User_ID;");*/
            db.resultSet = db.statement.executeQuery("SELECT A.Username FROM user AS A,"
                    + " depends AS B, projects AS C WHERE C.ProjectName = '" + result.get(j).getName() + "' AND "
                    + "B.project = '" + result.get(j).getName() + "' AND B.user = A.Username;");
            while (db.resultSet.next()) {
                result.get(j).addUser(db.resultSet.getString(1));
            }

        }

        return result;

    }
}
